package vn.ugame.model.dal;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import vn.ugame.exception.DataConnectionFailed;
import vn.ugame.exception.InvalidEntityClass;
import vn.ugame.model.entity.Account;
import vn.ugame.model.entity.Customer;

public class AccountDA {

    public Account findAccountById(String id)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {
        Account account = null;
        if (id != null && !id.isEmpty()) {
            List<Account> accounts = findAccounts(id, null);
            if (accounts.size() > 0) {
                account = accounts.get(0);
            }
        }
        return account;
    }

    public Account findAccountByEmail(String email)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {
        Account account = null;
        if (email != null && !email.isEmpty()) {
            List<Account> accounts = findAccounts(null, email);
            if (accounts.size() > 0) {
                account = accounts.get(0);
            }
        }
        return account;
    }

    public Customer findCustomerByEmail(String email)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {
        Customer customer = null;
        if (email != null && !email.isEmpty()) {
            List<Customer> customers = findCustomers(null, null, email, null, null, null);
            if (customers.size() > 0) {
                customer = customers.get(0);
            }
        }
        return customer;
    }

    public Customer findCustomerByFullName(String fullName)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {
        Customer customer = null;
        if (fullName != null && !fullName.isEmpty()) {
            List<Customer> customers = findCustomers(null, null, null, fullName, null, null);
            if (customers.size() > 0) {
                customer = customers.get(0);
            }
        }
        return customer;
    }

    public Customer findCustomerByPhoneNumber(String phoneNumber)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {
        Customer customer = null;
        if (phoneNumber != null && !phoneNumber.isEmpty()) {
            List<Customer> customers = findCustomers(null, null, null, null, phoneNumber, null);
            if (customers.size() > 0) {
                customer = customers.get(0);
            }
        }
        return customer;
    }

    public List<Customer> findCustomers(String customerId, String accountId, String email,
            String fullName, String phoneNumber, Boolean newsletter)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {
        List<Customer> customerList = new ArrayList<Customer>();

        Connection conn = ConnectionFactory.getInstance().createDBConnection();

        String query = "{ call findCustomers(?,?,?,?,?,?) }";
        CallableStatement cs = conn.prepareCall(query);
        cs.setString(1, customerId);
        cs.setString(2, accountId);
        cs.setString(3, email);
        cs.setString(4, fullName);
        cs.setString(5, phoneNumber);
        if (newsletter == null) {
            cs.setNull(6, Types.BIT);
        } else {
            cs.setBoolean(6, newsletter.booleanValue());
        }
        ResultSet rs = cs.executeQuery();
        EntityFactory factory = EntityFactory.createEntityFactory();
        while (rs.next()) {
            Customer customer = factory.createEntity(rs, Customer.class);
            customerList.add(customer);
        }
        rs.close();
        cs.close();
        conn.close();
        return customerList;
    }

    public List<Account> findAccounts(String id, String email)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {
        List<Account> accountList = new ArrayList<Account>();

        Connection conn = ConnectionFactory.getInstance().createDBConnection();

        String query = "{ call findAccounts(?,?) }";
        CallableStatement cs = conn.prepareCall(query);
        cs.setString(1, id);
        cs.setString(2, email);

        ResultSet rs = cs.executeQuery();
        EntityFactory factory = EntityFactory.createEntityFactory();
        while (rs.next()) {
            Account account = factory.createEntity(rs, Account.class);
            accountList.add(account);
        }
        rs.close();
        cs.close();
        conn.close();
        return accountList;
    }

    public void register(String email, String password, String fullName,
            String phoneNumber, boolean newsletter, String uCoin)
            throws DataConnectionFailed, SQLException, InvalidEntityClass {

        Connection conn = ConnectionFactory.getInstance().createDBConnection();

        String query = "{ call register(?,?,?,?,?,?) }";
        CallableStatement cs = conn.prepareCall(query);
        cs.setString(1, email);
        cs.setString(2, password);
        cs.setString(3, fullName);
        cs.setString(4, phoneNumber);
        cs.setBoolean(5, newsletter);
        cs.setString(6, uCoin);

        cs.execute();

        cs.close();
        conn.close();
    }
    
    public void rechargeUcoin(String customerId, String uCoin) 
            throws DataConnectionFailed, SQLException{
        Connection conn = ConnectionFactory.getInstance().createDBConnection();

        String query = "{ call rechargeUcoin(?,?) }";
        CallableStatement cs = conn.prepareCall(query);
        cs.setString(1, customerId);
        cs.setString(6, uCoin);

        cs.execute();

        cs.close();
        conn.close();
    }
}
